﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace QLCN
{
    public partial class FrmDM_User : Form
    {
        private string ChuoiKetNoi = "";
        private string chedo = "";
        private string Ten = "";
        private SqlDataAdapter sqlDataAdapter;
        private SqlConnection sqlConnection;
        private SqlCommand sqlCommand;
        private SqlDataReader sqlDataReader;
        private DataSet sqlDataSet;
        private DataTable sqlTable;
        private String Maso;
        private String id;
        private int Quyen;
        private String MaTinh=null;
        private string MaQuanHuyen=null;
        private string MaPhuong = null;
         
        public FrmDM_User()
        {
            InitializeComponent();
        }

        //ham ma hoa pass
        public string MD5(string chuoi)
        {
            string pass_md5 = "";
            byte[] mang = System.Text.Encoding.UTF8.GetBytes(chuoi);

            MD5CryptoServiceProvider my_md5 = new MD5CryptoServiceProvider();
            mang = my_md5.ComputeHash(mang);

            foreach (byte b in mang)
            {
                pass_md5 += b.ToString("X2");

            }

            return pass_md5;
        }

        private void FrmDM_User_Load(object sender, EventArgs e)
        {
            ChuoiKetNoi = "Data Source = " + Chung.ServerName + ";" +
                          "Initial Catalog = " + Chung.DatabaseName + ";" +
                          "persist security info = true;" +
                          "User Id=" + Chung.UserDatabase +
                          "; Password='" + Chung.PasswordDatabase +
                          "'; Connect Timeout =50";
            sqlConnection = new SqlConnection(ChuoiKetNoi);
            sqlConnection.Open();
            Display();
            txtTen.Enabled = false;
            txtMK1.Enabled = false;
            txtMK2.Enabled = false;
            txtHoten.Enabled = false;
            CboQuyen.Enabled = false;
            btnLuu.Enabled = false;
            Cb1.Enabled = false;
            //load dữ liệu phân quyền
            try
            {
                string sql = "select * from DmQuyen";
                sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
                sqlTable = new DataTable();
                sqlDataAdapter.Fill(sqlTable);
                sqlDataAdapter.Dispose();
                CboQuyen.DataSource = sqlTable;
                CboQuyen.DisplayMember = "TenQuyen";
                CboQuyen.ValueMember = "IdQuyen";
            }
            catch
            { }
            //chuyển dữ liệu lên CboTinh
          try
            {
                string dulieu = "", table = "";
                table = " DmTinh order by TenTinh ";
                dulieu = " Matinh, TenTinh ";
                sqlCommand = new SqlCommand("spDislay", sqlConnection);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                //lấy giá trị vào biến    
                sqlCommand.Parameters.AddWithValue("@dulieu", dulieu.Trim());
                sqlCommand.Parameters.AddWithValue("@Table", table.Trim());
                //thực thi truy van 
                sqlCommand.ExecuteNonQuery();
                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                sqlDataSet = new DataSet();
                sqlDataAdapter.Fill(sqlDataSet, "DmTinh");
                sqlTable = sqlDataSet.Tables[0];
                //MessageBox.Show(sqlTable.Rows.Count.ToString());
                //chuyen len luoi
                cboTinh.DataSource = sqlTable;
                cboTinh.DisplayMember = "TenTinh";
                cboTinh.ValueMember = "Matinh";
                cboTinh.Text = "";
                sqlDataAdapter.Dispose();
                sqlCommand.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi dữ liệu Tỉnh: " + ex.Message);
            }
        }

        private void FrmDM_User_KeyDown(object sender, KeyEventArgs e)
        {
            // MessageBox.Show(e.KeyValue.ToString());
            switch (e.KeyValue)
            {

                case 113:
                    Luu();
                    break;

                case 27:
                    {
                        sqlConnection.Close();
                        this.Close();
                    }

                    break;

                case 114:
                    {
                        Moi();
                        break;
                    }

                case 115:
                    {
                        Sua();
                        break;
                    }

                case 117:
                    {
                        Xoa();
                        break;
                    }

                case 112:
                    {
                        //Tro giup
                        break;
                    }

            }
        }

        private void Display()
        {
            try
            {
                sqlDataAdapter = new SqlDataAdapter("spAdmin_truyvan", sqlConnection);
                sqlDataSet = new DataSet();
                sqlDataAdapter.Fill(sqlDataSet, "Account");
                sqlTable = sqlDataSet.Tables[0];
                //chuyen len luoi
                dataGridView1.DataSource = sqlTable;
                dataGridView1.AutoGenerateColumns = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Lỗi chuyển dữ liệu lên lưới!");
            }
        }

        private void Setcontrol(bool T)
        {
            if (T)
            {
                txtTen.Enabled = true;
                txtMK1.Enabled = true;
                txtMK2.Enabled = true;
                CboQuyen.Enabled = true;
                cboTinh.Enabled = true;
                cboHuyen.Enabled = true;
                cboPhuong.Enabled = true;
                btnThem.Enabled = false;
                btnLuu.Enabled = true;
                txtHoten.Enabled = true;
                Cb1.Enabled = true;

            }
            else
            {
                //txtTen.Enabled = false;
                //txtMK1.Enabled = false;
                btnThem.Enabled = true;
                btnLuu.Enabled = false;
            }
        }

        private void btnThem_Click(object sender, EventArgs e)
        {
            Moi();
        }

        private void btnSua_Click(object sender, EventArgs e)
        {
            Sua();
        }

        private void btnLuu_Click(object sender, EventArgs e)
        {
            Luu();
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            Xoa();
        }


        private void BntLui_Click(object sender, EventArgs e)
        {
            
            sqlConnection.Close();
            this.Close();
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            int row = e.RowIndex;
            string tinhtrang;
            //txtId.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString();
            id = dataGridView1.CurrentRow.Cells[0].Value.ToString();

            txtTen.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString();
            txtMK1.Text = MD5(dataGridView1.CurrentRow.Cells[2].Value.ToString());
            txtMK2.Text = MD5(dataGridView1.CurrentRow.Cells[2].Value.ToString());
            txtHoten.Text = dataGridView1.CurrentRow.Cells[3].Value.ToString();
            CboQuyen.Text= dataGridView1.CurrentRow.Cells[4].Value.ToString();
            Quyen = Convert.ToInt16(dataGridView1.CurrentRow.Cells[8].Value.ToString());
            cboTinh.Text= dataGridView1.CurrentRow.Cells[5].Value.ToString();
            MaTinh = dataGridView1.CurrentRow.Cells[11].Value.ToString();
            
           
            
            tinhtrang = dataGridView1.CurrentRow.Cells[10].Value.ToString();
           // MessageBox.Show("Mã tỉnh: "+ MaTinh +"   Mã Huyện: " + MaQuanHuyen + " Mã Phường: " + MaPhuong);

            //xác lập cho trình trạng tồn tại
            if (tinhtrang.Trim() == "1")
            {
                Cb1.Checked = true;
            }
            else
            {
                Cb1.Checked = false;
            }

            //Lấy lại Huỵen
            MaQuanHuyen = dataGridView1.CurrentRow.Cells[12].Value.ToString();            
            sqlConnection = new SqlConnection(ChuoiKetNoi);
            sqlConnection.Open();
            string sql = "select * from DmHuyen Where MaTinh ='" + MaTinh.Trim() + "'";
            sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
            sqlTable = new DataTable();
            sqlDataAdapter.Fill(sqlTable);
            sqlDataAdapter.Dispose();
            cboHuyen.DataSource = sqlTable;
            cboHuyen.DisplayMember = "TenHuyen";
            cboHuyen.ValueMember = "MaHuyen";
            cboHuyen.Text = dataGridView1.CurrentRow.Cells[6].Value.ToString();

            //Lấy lại tên điạ phường            
            MaPhuong = dataGridView1.CurrentRow.Cells[13].Value.ToString();
            sqlConnection = new SqlConnection(ChuoiKetNoi);
            sqlConnection.Open();
            sql = "select * from DmDiaPhuong Where MaHuyen ='" + MaQuanHuyen.Trim() + "'";
            sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
            sqlTable = new DataTable();
            sqlDataAdapter.Fill(sqlTable);
            sqlDataAdapter.Dispose();
            cboPhuong.DataSource = sqlTable;
            cboPhuong.DisplayMember = "TenPhuong";
            cboPhuong.ValueMember = "MaPhuong";
            cboPhuong.Text = dataGridView1.CurrentRow.Cells[7].Value.ToString();


            txtTen.Enabled = true;
            txtMK1.Enabled = true;
            txtMK2.Enabled = true;
            txtHoten.Enabled = true;
            CboQuyen.Enabled = true;
            cboTinh.Enabled = true;
            cboPhuong.Enabled = true;
            Cb1.Enabled = true;
        }

        private void Moi()
        {
            txtTen.Text = "";
            txtMK1.Text = "";
            txtHoten.Text = "";
            cboTinh.Text = "";
            cboHuyen.Text = "";
            cboPhuong.Text = "";
            MaTinh = null;
            MaQuanHuyen = null;
            MaPhuong = null;
            Cb1.Checked = true;
            btnThem.Enabled = false;
            btnSua.Enabled = true;
            btnLuu.Enabled = true;
            txtTen.Focus();
            Setcontrol(true);
            chedo = "them";
        }

        private void Luu()
        {
            try
            {

                string pass = txtMK1.Text.ToString().Trim();
                string passMD5 = MD5(pass);
                int ok = 0;
                

                if (CboQuyen.Text != "")
                {
                    Quyen = Convert.ToInt16(CboQuyen.SelectedValue);
                }

                {
                    if (Cb1.Checked == true)
                    {
                        ok = 1;
                    }
                    else
                    {
                        ok = 0;
                    }
                }

                switch (chedo)
                {
                    case "them":
                        {
                            if ((txtTen.Text != "") && (txtMK1.Text != "") && (CboQuyen.Text != ""))
                            {
                                if (txtMK1.Text.ToString().Trim() == txtMK2.Text.ToString().Trim())
                                {

                                    sqlCommand = new SqlCommand("spAdmin_nhapmoi", sqlConnection);
                                    sqlCommand.CommandType = CommandType.StoredProcedure;
                                    //lấy giá trị vào biến    
                                    sqlCommand.Parameters.AddWithValue("@Ten", txtTen.Text.Trim());
                                    sqlCommand.Parameters.AddWithValue("@Pass", passMD5.Trim());
                                    sqlCommand.Parameters.AddWithValue("@HoTen", txtHoten.Text.Trim());
                                    sqlCommand.Parameters.AddWithValue("@Quyen", Quyen);
                                    sqlCommand.Parameters.AddWithValue("@Active", ok);
                                    sqlCommand.Parameters.AddWithValue("@Tinh", MaTinh);
                                    sqlCommand.Parameters.AddWithValue("@QuanHuyen", MaQuanHuyen);
                                    sqlCommand.Parameters.AddWithValue("@Phuong", MaPhuong);
                                    //thực thi truy van 
                                    sqlCommand.ExecuteNonQuery();
                                    sqlCommand.Dispose();

                                    Display();
                                    Setcontrol(false);
                                    btnThem.Enabled = false;
                                    btnSua.Enabled = true;
                                    txtTen.Text = "";
                                    txtMK1.Text = "";
                                    txtMK2.Text = "";
                                    txtHoten.Text = "";
                                    CboQuyen.Text = "";
                                    cboTinh.Text = "";
                                    cboHuyen.Text = "";
                                    cboPhuong.Text = "";
                                }
                                else
                                {
                                    MessageBox.Show("Mat Khau va Xac Nhan Lai Mat Khau chua dung", "Thông báo");
                                    txtMK1.Focus();
                                }
                            }
                            else
                            {
                                MessageBox.Show("Bạn nhập chưa đầy đủ dữ liệu", "Thông báo");
                                txtTen.Focus();
                            }
                            break;
                        }
                    case "sua":
                        {
                            string dulieu, dieukien;

                            if (txtMK1.Text != "")
                            {
                                dulieu = " Ten = N'" + txtTen.Text.Trim() + "',HoTen=N'" + txtHoten.Text.Trim() + "', Pass=N'" + passMD5.Trim() + "', Quyen=N'" + Quyen + "', Active='" + ok + "', ";
                                dulieu = dulieu + "Tinh= '" + MaTinh + "'," + "QuanHuyen='" + MaQuanHuyen + "'," + "Phuong= '" + MaPhuong + "'";

                            }
                            else
                            {

                                dulieu = " Ten = N'" + txtTen.Text.Trim() + "',HoTen=N'" + txtHoten.Text.Trim() + "', Quyen=N'" + Quyen + "', Active='" + ok + "' ";
                                dulieu = dulieu + "Tinh= '" + MaTinh + "'," + "QuanHuyen='" + MaQuanHuyen + "'," + "Phuong= '" + MaPhuong + "'";

                            }
                            dieukien = "IdUser = '" + Convert.ToInt16(id.Trim()) + "'";


                            sqlCommand = new SqlCommand("spAdmin_capnhat", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;

                            //lấy giá trị vào biến    
                            sqlCommand.Parameters.AddWithValue("@dulieu", dulieu.Trim());
                            sqlCommand.Parameters.AddWithValue("@Dieukien", dieukien.Trim());

                            //thực thi truy van 
                            sqlCommand.ExecuteNonQuery();
                            sqlCommand.Dispose();

                            Display();
                            Setcontrol(false);
                            btnSua.Enabled = true;
                            btnThem.Enabled = true;
                            btnLuu.Enabled = false;
                            txtTen.Text = "";
                            txtMK1.Text = "";
                            txtMK2.Text = "";
                            txtHoten.Text = "";
                            Cb1.Checked = false;
                            CboQuyen.Text = "";
                            cboTinh.Text = "";
                            cboHuyen.Text = "";
                            cboPhuong.Text = "";
                            txtTen.Focus();
                            break;
                        }

                }
                btnThem.Enabled = true;
            }

            catch (Exception ex)
            {
                MessageBox.Show("Có lỗi cập nhật : " + ex.Message, "Thông báo :");
            }
        }
        private void Sua()
        {
           
            btnThem.Enabled = true;
            Setcontrol(true);

            chedo = "sua";
            Maso = txtTen.Text;
            btnThem.Enabled = true;
            btnSua.Enabled = false;
            btnLuu.Enabled = true;
            btnLuu.Focus();
        }

        private void Xoa()
        {
            if (MessageBox.Show("Bạn muốn xóa Người Dùng : " + txtHoten.Text.ToString().Trim() + " ? ", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                sqlCommand = new SqlCommand("spAdmin_Xoa", sqlConnection);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                //lấy giá trị vào biến                                
                sqlCommand.Parameters.AddWithValue("@Ten", Convert.ToInt16(id.Trim()));
                //thực thi truy van                                     
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Dispose();
                txtTen.Text = "";
                txtHoten.Text = "";
                CboQuyen.Text = "";
                cboTinh.Text = "";
                cboHuyen.Text = "";
                cboPhuong.Text = "";
                Display();
            }
        }

        private void CboQuyen_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                switch (Convert.ToInt16(CboQuyen.SelectedValue))
                {
                    case 1:
                    case 2:
                    case 5:
                    case 6:    //Quản trị hệ thống
                        cboTinh.Enabled = true;
                        cboHuyen.Enabled = true;
                        cboPhuong.Enabled = true;
                        break;
                    case 3:     //Cấp quận huyện
                        cboTinh.Enabled = true;
                        cboHuyen.Enabled = true;
                        cboPhuong.Enabled = false;
                        break;
                    case 4:     //Cấp xã phương
                        cboTinh.Enabled = true;
                        cboHuyen.Enabled = true;
                        cboPhuong.Enabled = true;
                        break;
                }
                Quyen=Convert.ToInt16(CboQuyen.SelectedValue.ToString().Trim());
            }
            catch
            {

            }
          
        }

        private void cboTinh_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                MaTinh = cboTinh.SelectedValue.ToString().Trim();
                if (MaTinh != "")
                {
                    string dulieu = "", table = "";

                    table = " DmHuyen where MaTinh = '" + MaTinh + "' order by TenHuyen";
                    dulieu = "MaHuyen, TenHuyen ";

                    sqlCommand = new SqlCommand("spDislay", sqlConnection);
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    //lấy giá trị vào biến    
                    sqlCommand.Parameters.AddWithValue("@dulieu", dulieu.Trim());
                    sqlCommand.Parameters.AddWithValue("@Table", table.Trim());

                    //thực thi truy van 
                    sqlCommand.ExecuteNonQuery();

                    sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                    sqlDataSet = new DataSet();
                    sqlDataAdapter.Fill(sqlDataSet, "DmHuyen");

                    sqlTable = sqlDataSet.Tables[0];

                    //chuyen len luoi
                    cboHuyen.DataSource = sqlTable;
                    cboHuyen.DisplayMember = "TenHuyen";
                    cboHuyen.ValueMember = "MaHuyen";
                    cboHuyen.Text = "";
                    sqlDataAdapter.Dispose();
                    sqlCommand.Dispose();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi dữ liệu Quận/Huyện!" + ex.Message);
            }
            MaTinh = cboTinh.SelectedValue.ToString().Trim();
        }

        private void cboHuyen_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                string dulieu = "", table = "";
                table = " DmDiaPhuong where MaHuyen = '" + cboHuyen.SelectedValue.ToString().Trim() + "' order by TenPhuong";
                dulieu = " MaPhuong,TenPhuong ";
                sqlCommand = new SqlCommand("spDislay", sqlConnection);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                //lấy giá trị vào biến    
                sqlCommand.Parameters.AddWithValue("@dulieu", dulieu.Trim());
                sqlCommand.Parameters.AddWithValue("@Table", table.Trim());
                //thực thi truy van 
                sqlCommand.ExecuteNonQuery();
                sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                sqlDataSet = new DataSet();
                sqlDataAdapter.Fill(sqlDataSet, "DmDiaPhuong");
                sqlTable = sqlDataSet.Tables[0];
                //chuyen len luoi
                cboPhuong.DataSource = sqlTable;
                cboPhuong.Text = "";
                cboPhuong.DisplayMember = "TenPhuong";
                cboPhuong.ValueMember = "MaPhuong";
                sqlDataAdapter.Dispose();
                sqlCommand.Dispose();
            }
            catch (Exception ex)
          
            {
                MessageBox.Show("Lỗi dữ liệu Phường/Xã!" + ex.Message);
            }       
            MaQuanHuyen = cboHuyen.SelectedValue.ToString().Trim();
        }

        private void cboTinh_Click(object sender, EventArgs e)
        {
            sqlConnection = new SqlConnection(ChuoiKetNoi);
            sqlConnection.Open();
            string sql = "select * from DmTinh order by TenTinh";
            sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
            sqlTable = new DataTable();
            sqlDataAdapter.Fill(sqlTable);
            sqlDataAdapter.Dispose();
            cboTinh.DataSource = sqlTable;
            cboTinh.DisplayMember = "TenTinh";
            cboTinh.ValueMember = "MaTinh";
        }

        private void cboHuyen_Click(object sender, EventArgs e)
        {
            sqlConnection = new SqlConnection(ChuoiKetNoi);
            sqlConnection.Open();
            string sql = "select * from DmHuyen order by TenHuyen";
            sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
            sqlTable = new DataTable();
            sqlDataAdapter.Fill(sqlTable);
            sqlDataAdapter.Dispose();
            cboTinh.DataSource = sqlTable;
            cboTinh.DisplayMember = "TenHuyen";
            cboTinh.ValueMember = "MaHuyen";
        }

        private void cboPhuong_SelectedIndexChanged(object sender, EventArgs e)
        {
            MaPhuong= cboPhuong.SelectedValue.ToString().Trim();
        }
    }
}